package com.stormrage.edu.dao.learn.impl;

import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.stormrage.edu.dao.learn.LeaBunchDao;
import com.stormrage.entity.excel.ExmOperExcel;
import com.stormrage.entity.excel.ExmUsuaExcel;
import com.stormrage.entity.learn.LeaBunch;
import com.stormrage.entity.learn.rep.LeaBunchRep;
import com.stormragetech.core.dao.MysBaseDao;
import com.stormragetech.core.exception.StormrageTechException;

/**
 * @Title: LeaClassDaoImpl.java
 * @Package com.stormrage.edu.dao.learn.impl
 * @Description: 班级 Dao
 * @author www.stormragetech.com
 * @date 2016年4月13日
 * @version V1.0
 */
@Repository
public class LeaBunchDaoImpl extends MysBaseDao implements LeaBunchDao {

	private static final Logger logger = LoggerFactory.getLogger(LeaBunchDaoImpl.class);

	@Override
	public Integer create(final LeaBunch arg0) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 Create 】- 开始运行 ");

		final String query = "INSERT INTO lea_bunch (bunch, term_id, type, locked) VALUES"
				+ " (:bunch, :termId, :type, :locked)";
		logger.debug(" 数据库执行语句为 " + query);

		Integer index = create(query, arg0);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 Create 】- 完成运行 ");
		return index;

	}

	@Override
	public void update(final LeaBunch arg0) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 Update 】- 开始运行 ");

		final String query = "UPDATE lea_bunch SET locked = :locked WHERE id = :id";
		logger.debug(" 数据库执行语句为 " + query);

		update(query, arg0);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 Update 】- 完成运行 ");

	}

	@Override
	public void delete(Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 delete 】- 开始运行 ");

		final String query = "DELETE FROM lea_bunch WHERE id = :id";
		logger.debug(" 数据库执行语句为 " + query);

		update(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 delete 】- 完成运行 ");

	}

	@Override
	public void deleteByTermId(Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 DeleteByTermId 】- 开始运行 ");

		final String query = "DELETE FROM lea_bunch WHERE term_id = :id";
		logger.debug(" 数据库执行语句为 " + query);

		update(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 DeleteByTermId 】- 完成运行 ");

	}

	@Override
	public Integer queryCountByTermEnt(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryByTermEnt 】- 开始运行 ");

		final String query = "SELECT COUNT(*) FROM lea_bunch AS a, lea_bunch_apply AS b"
				+ " WHERE a.id = b.bunch_id AND a.term_id = :termId AND b.ent_id = :entId";
		logger.debug(" 数据库执行语句为 " + query);

		Integer count = queryCount(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryByTermEnt 】- 完成运行 ");
		return count;

	}

	@Override
	public Integer queryCountByLock(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountByLock 】- 开始运行 ");

		final String query = "SELECT COUNT(*) FROM lea_bunch WHERE locked = :locked " + map.get("query");
		logger.debug(" 数据库执行语句为 " + query);

		Integer count = queryCount(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountByLock 】- 完成运行 ");
		return count;

	}

	@Override
	public Integer queryCountForApply(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForApply 】- 开始运行 ");

		final String query = "SELECT COUNT(*) FROM lea_bunch AS a, lea_bunch_apply AS b, lea_term AS c, edu_major AS d,"
				+ " ent_enterprise AS e WHERE a.id = b.bunch_id AND a.term_id = c.id AND"
				+ " c.major_id = d.id AND e.id = b.ent_id AND a.type = :type" + " AND b.locked = 0 " + map.get("query");
		logger.debug(" 数据库执行语句为 " + query);

		Integer count = queryCount(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForApply 】- 完成运行 ");
		return count;

	}

	@Override
	public Integer queryCountForSingle(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 开始运行 ");

		final String query = "SELECT COUNT(*) FROM lea_bunch AS a, lea_term AS b, edu_major AS c"
				+ " WHERE a.term_id = b.id AND b.major_id = c.id AND a.type = :type " + map.get("query");
		logger.debug(" 数据库执行语句为 " + query);

		Integer count = queryCount(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 完成运行 ");
		return count;

	}

	@Override
	public Integer queryCountForLearn(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 开始运行 ");

		final String query = "SELECT COUNT(*) FROM lea_bunch AS a, lea_term AS b, edu_major AS c"
				+ " WHERE a.term_id = b.id AND b.major_id = c.id AND a.locked = 0  AND a.term_id = :termId "
				+ map.get("query");

		logger.debug(" 数据库执行语句为 " + query);

		Integer count = queryCount(query, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 完成运行 ");
		return count;

	}

	@Override
	public LeaBunch queryById(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 开始运行 ");

		final String query = "SELECT * FROM lea_bunch WHERE id = :id";
		logger.debug(" 数据库执行语句为 " + query);

		LeaBunch entity = queryObject(query, LeaBunch.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 完成运行 ");
		return entity;

	}

	@Override
	public LeaBunchRep queryRepById(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 开始运行 ");

		final String query = "SELECT a.*, b.id AS applyId, b.ent_id, b.excel, b.apply_date FROM lea_bunch AS a,"
				+ " lea_bunch_apply AS b WHERE a.id = b.bunch_id AND a.id = :id AND b.locked = 0";
		logger.debug(" 数据库执行语句为 " + query);

		LeaBunchRep entity = queryObject(query, LeaBunchRep.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryCountForSingle 】- 完成运行 ");
		return entity;

	}

	@Override
	public List<LeaBunchRep> queryListByLock(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryList 】- 开始运行 ");

		final String query = "SELECT a.id, a.bunch, b.term, c.major FROM lea_bunch AS a, lea_term AS b, edu_major AS c"
				+ " WHERE a.term_id = b.id AND b.major_id = c.id AND a.locked = :locked " + map.get("query")
				+ " LIMIT :startPage, :size";
		logger.debug(" 数据库执行语句为 " + query);

		List<LeaBunchRep> array = queryList(query, LeaBunchRep.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryList 】- 完成运行 ");
		return array;

	}

	@Override
	public List<LeaBunchRep> queryListForApply(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryList 】- 开始运行 ");

		final String query = "SELECT a.id, b.id AS applyId, a.bunch, b.excel, b.apply_date, c.term, d.major, e.enterprise"
				+ " FROM lea_bunch AS a, lea_bunch_apply AS b, lea_term AS c, edu_major AS d,"
				+ " ent_enterprise AS e WHERE a.id = b.bunch_id AND a.term_id = c.id AND"
				+ " c.major_id = d.id AND e.id = b.ent_id AND a.type = :type AND b.locked = 0 " + map.get("query")
				+ " LIMIT :startPage, :size";
		logger.debug(" 数据库执行语句为 " + query);

		List<LeaBunchRep> array = queryList(query, LeaBunchRep.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryList 】- 完成运行 ");
		return array;

	}

	@Override
	public List<LeaBunchRep> queryListForSingle(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryListForSingle 】- 开始运行 ");

		final String query = "SELECT a.id, a.bunch, b.term, c.major FROM lea_bunch AS a, lea_term AS b, edu_major AS c"
				+ " WHERE a.term_id = b.id AND b.major_id = c.id AND a.type = :type " + map.get("query")
				+ " LIMIT :startPage, :size";
		logger.debug(" 数据库执行语句为 " + query);

		List<LeaBunchRep> array = queryList(query, LeaBunchRep.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryListForSingle 】- 完成运行 ");
		return array;

	}

	@Override
	public List<LeaBunchRep> queryListForLearn(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryListForLearn 】-【 STARTED 】");

		final String query = "SELECT a.id, a.bunch, b.term, c.major, b.apply_date_end, b.study_date_end,"
				+ " b.exam_date_end FROM lea_bunch AS a, lea_term AS b, edu_major AS c"
				+ " WHERE a.term_id = b.id AND b.major_id = c.id AND a.locked = 0 AND a.term_id = :termId "
				+ map.get("query") + " LIMIT :startPage, :size";
		logger.debug(" 数据库执行语句为 " + query);

		List<LeaBunchRep> array = queryList(query, LeaBunchRep.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryListForLearn 】-【 COMPLETED 】");
		return array;

	}

	@Override
	public List<LeaBunchRep> queryRepListForNotice(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryListForLearn 】-【 STARTED 】");

		final String query = "SELECT a.id, a.bunch, b.term, c.major FROM lea_bunch AS a, lea_term AS b, edu_major AS c"
				+ " WHERE a.term_id = b.id AND b.major_id = c.id AND a.locked = 0 AND b.exam_date_end >= :datetime";
		logger.debug(" 数据库执行语句为 " + query);

		List<LeaBunchRep> array = queryList(query, LeaBunchRep.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 QueryListForLearn 】-【 COMPLETED 】");
		return array;

	}

	@Override
	public List<ExmOperExcel> queryOperListForExcel(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 queryUsuaListForExcel 】-【 STARTED 】");

		final String query = "SELECT a.id, b.stu_no, c.real_name AS name, 0 AS score FROM lea_student AS a, stu_acct AS b,"
				+ " stu_info AS c WHERE a.acct_id = b.id AND b.id = c.acct_id AND a.bunch_id = :bunchId";
		logger.debug(" 数据库执行语句为 " + query);

		List<ExmOperExcel> array = queryList(query, ExmOperExcel.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 queryUsuaListForExcel 】-【 COMPLETED 】");
		return array;

	}

	@Override
	public List<ExmUsuaExcel> queryUsuaListForExcel(final Map<String, Object> map) throws StormrageTechException {

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 queryUsuaListForExcel 】-【 STARTED 】");

		final String query = "SELECT a.id, b.stu_no, c.real_name AS name, 0 AS score FROM lea_student AS a, stu_acct AS b,"
				+ " stu_info AS c WHERE a.acct_id = b.id AND b.id = c.acct_id AND a.bunch_id = :bunchId";
		logger.debug(" 数据库执行语句为 " + query);

		List<ExmUsuaExcel> array = queryList(query, ExmUsuaExcel.class, map);

		logger.debug(" 【 Dao 】-【 LeaBunchDao 】-【 queryUsuaListForExcel 】-【 COMPLETED 】");
		return array;

	}

}
